﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Net.Fan.SmallPig.DataBase;
using System.Data;
using Net.Fan.SmallPig.Common;

namespace Net.Fan.SmallPig.DataAccess
{
    public class JobDA
    {

        public static long AddNewJob(long workflowId, string summary, string customer, long count, string description, string currentState, DateTime? planFinishDate, string createUser)
        {
            string sql = "INSERT INTO Jobs (WorkflowId, Summary, Customer, Count, Description, CurrentState, CreateUser,PlanFinishDate, CreateTime) VALUES (@WorkflowId, @Summary, @Customer, @Count, @Description, @CurrentState, @CreateUser, @PlanFinishDate, GetDate()) ";
            sql += " select @@identity";
            SqlParameter[] paras = new SqlParameter[8];
            paras[0] = new SqlParameter("@WorkflowId", workflowId);
            paras[1] = new SqlParameter("@Description", description);
            paras[2] = new SqlParameter("@Count", count);
            paras[3] = new SqlParameter("@Customer", customer);
            paras[4] = new SqlParameter("@CurrentState", currentState);
            paras[5] = new SqlParameter("@CreateUser", createUser);
            paras[6] = new SqlParameter("@Summary", summary);
            paras[7] = new SqlParameter("@PlanFinishDate", planFinishDate);
            decimal dd = (decimal)DA_Base.ExecuteScalar(sql, paras);
            return Convert.ToInt64(dd);
        }

        public static DataRow GetJob(string jobId)
        {
            long jid = 0;
            long.TryParse(jobId, out jid);
            return GetJob(jid);
        }

        public static DataRow GetJob(long jobId)
        {
            string sql = "select * from Jobs where JobId = @JobId";

            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@JobId", jobId);

            DataSet ds = DA_Base.RunReturnDataset(sql, paras);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0];
            }
            else
            {
                return null;
            }
        }

        public static DataTable GetWhoesJobs(string userId)
        {
            string sql = "select distinct j.* from Jobs j ,Activities a , ActivityOperators o where j.workflowId=a.workflowId and j.currentState=a.currentState and o.activityId=a.activityId and o.operator=@UserId";

            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@UserId", userId);

            DataSet ds = DA_Base.RunReturnDataset(sql, paras);
            return ds.Tables[0];
        }


        public static DataTable GetAllNoFinishedJobs()
        {
            string sql = "select j.* from Jobs j ,Workflows w where j.workflowId=w.workflowId and j.CurrentState != w.EndState";

            //SqlParameter[] paras = new SqlParameter[1];
            //paras[0] = new SqlParameter("@UserId", userId);

            DataSet ds = DA_Base.RunReturnDataset(sql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 已过期
        /// </summary>
        /// <returns></returns>
        public static DataTable GetOverdueJobs()
        {
            string sql = "select j.* from Risks r ,Jobs j where j.JobId=r.JobId and r.Type='已逾期'";

            //SqlParameter[] paras = new SqlParameter[1];
            //paras[0] = new SqlParameter("@UserId", userId);

            DataSet ds = DA_Base.RunReturnDataset(sql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 快到期
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDueSoonJobs()
        {
            string sql = "select j.* from Risks r ,Jobs j where j.JobId=r.JobId and r.Type='快到期'";

            //SqlParameter[] paras = new SqlParameter[1];
            //paras[0] = new SqlParameter("@UserId", userId);

            DataSet ds = DA_Base.RunReturnDataset(sql);
            return ds.Tables[0];
        }

        public static int SetCurrentState(long jobId, string state)
        {
            string sql = "Update Jobs set CurrentState=@CurrentState where JobId = @JobId";

            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@CurrentState", state);
            paras[1] = new SqlParameter("@JobId", jobId);
            return DA_Base.ExecuteNonQuery(sql, paras);
        }

        public static long LogOperation(long jobId, string operation, string note, string operateUser)
        {
            string sql = "INSERT INTO ActionHistory (JobId,Operation,Note,Operator,HappenTime) VALUES (@JobId,@Operation,@Note,@Operator,GetDate()) ";
            sql += " select @@identity";
            SqlParameter[] paras = new SqlParameter[5];
            paras[0] = new SqlParameter("@JobId", jobId);
            paras[1] = new SqlParameter("@Operation", operation);
            paras[2] = new SqlParameter("@Note", note);
            paras[3] = new SqlParameter("@Operator", operateUser);

            decimal dd = (decimal)DA_Base.ExecuteScalar(sql, paras);
            return Convert.ToInt64(dd);
        }

        public static DataTable GetOperationHistoy(long jobId)
        {
            string sql = "select * from ActionHistory H where H.JobId=@JobId";

            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@JobId", jobId);

            DataSet ds = DA_Base.RunReturnDataset(sql, paras);
            return ds.Tables[0];
        }

        public static DataTable SearchJobs(string[] keys, JobTimeStatus jobTimeStatus)
        {
            string sql = "select j.* from Jobs j ,Workflows w where j.summary like '%'+@Key1+'%' and j.workflowId=w.workflowId";

            switch (jobTimeStatus)
            {
                case JobTimeStatus.Running:
                    sql += " and j.CurrentState != w.EndState";
                    break;
                case JobTimeStatus.Finished:
                    sql += " and j.CurrentState = w.EndState";
                    break;
                case JobTimeStatus.All:
                    break;
                default:
                    break;
            }

            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@Key1", keys[0]);

            DataSet ds = DA_Base.RunReturnDataset(sql, paras);
            return ds.Tables[0];
        }

        public static int UpdateJob(long jobId, string summary, DateTime? planFinishTime, int? number, string unit, string customer, string description)
        {
            string sql = "Update Jobs set Summary=@Summary, Customer=@Customer, Count=@Count, Unit=@Unit, PlanFinishTime=@PlanFinishTime, Description=@Description  where JobId = @JobId";

            SqlParameter[] paras = new SqlParameter[7];
            paras[0] = new SqlParameter("@Summary", summary);
            paras[1] = new SqlParameter("@JobId", jobId);
            paras[2] = new SqlParameter("@Customer", customer);
            paras[3] = new SqlParameter("@Count", number);
            paras[4] = new SqlParameter("@Unit", unit);
            paras[5] = new SqlParameter("@Description", description);
            paras[6] = new SqlParameter("@PlanFinishTime", planFinishTime);

            //paras[] = new SqlParameter("@", );
            //paras[] = new SqlParameter("@", );

            return DA_Base.ExecuteNonQuery(sql, paras);
        }
    }
}
